IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ScriptPrimaryKeysTable'))
BEGIN
	DROP PROCEDURE MD3.ScriptPrimaryKeysTable
END
GO

CREATE Procedure MD3.ScriptPrimaryKeysTable

AS  
BEGIN

/*

	Generates a SQL script that can be saved to the PrimaryKeys folder, in MasterData format, that is used as the basis for all 
	FK constraint manipulation.



*/


PRINT '/*'
PRINT '	This script holds the MasterData about our Primary Keys'
PRINT '	Simply modify this metadata and rerun the MD3 deployment and any Primary Key changes'
PRINT '		will be reflected in your database.  '
PRINT ''
PRINT '	To add a new Primary Key ...Add a new INSERT line to the file.'
PRINT '	To modify a PK...Modify the values of the EXISTING check constraint'
PRINT '	To delete a PK...Set the DropPK flag to 1.  Do not actually delete the row.'
PRINT ''
PRINT ' This script can be automatically regenerated by running MD3.ScriptPrimaryKeysTable'
PRINT '*/'

SET NOCOUNT ON
PRINT 'SET NOCOUNT ON'
PRINT '--remove all old entries from table'
PRINT 'TRUNCATE TABLE MD3.PrimaryKeys'
PRINT ''
PRINT '--add new entries to table'


DECLARE @exec_str VARCHAR(MAX), @new_str VARCHAR(MAX), @quote VARCHAR(MAX) = '''';
SELECT @exec_str = 'INSERT INTO MD3.PrimaryKeys (SchemaName,TableName,PrimaryKeyName,KeyColumns,IsClustered,IdxFillFactor,IsPadded,FGorPartitionScheme,CompressionSetting,DropPK) VALUES('

DECLARE 
	@SchemaName VARCHAR(256),
	@TableName VARCHAR(256) ,
	@PrimaryKeyName	SYSNAME ,    
	@KeyColumns VARCHAR(MAX) ,
    @IsClustered	VARCHAR(256) ,  
	@IdxFillFactor VARCHAR(256) ,
	@IsPadded VARCHAR(256) ,
    @FGorPartitionScheme VARCHAR(MAX),
	@CompressionSetting VARCHAR(50),
	@DropPK VARCHAR(256) 


DECLARE dave CURSOR FOR 
	SELECT 
		SchemaName,TableName,PrimaryKeyName,KeyColumns,IsClustered,IdxFillFactor,IsPadded,FGorPartitionScheme,CompressionSetting,DropPK
	FROM MD3.PrimaryKeys
	ORDER BY SchemaName, TableName
OPEN dave
FETCH NEXT FROM dave INTO 
	@SchemaName ,
	@TableName  ,
	@PrimaryKeyName	,    
	@KeyColumns ,
    @IsClustered	,  
	@IdxFillFactor ,
	@IsPadded ,
    @FGorPartitionScheme ,
	@CompressionSetting ,
	@DropPK 
WHILE (@@FETCH_STATUS = 0)
BEGIN

	SELECT @new_str  = @exec_str + '''' + @SchemaName + ''',''' + @TableName + ''',''' + @PrimaryKeyName + ''',''' + @KeyColumns + ''','
	SELECT @new_str += @IsClustered + ',' + @IdxFillFactor + ',' + @IsPadded + ',''' + @FGorPartitionScheme + ''','''
	SELECT @new_str += @CompressionSetting + ''',' 
	SELECT @new_str += @DropPK +');'
	PRINT @new_str
	
	FETCH NEXT FROM dave INTO 
		@SchemaName ,
		@TableName  ,
		@PrimaryKeyName	,    
		@KeyColumns ,
		@IsClustered	,  
		@IdxFillFactor ,
		@IsPadded ,
		@FGorPartitionScheme ,
		@CompressionSetting ,
		@DropPK 
END;
CLOSE dave
DEALLOCATE dave


PRINT '--now building PrimaryKeys'
PRINT 'EXEC MD3.RunPrimaryKeys'
PRINT 'GO'
PRINT 'SET NOCOUNT OFF'

END
GO
